package com.fjnldx.ks.mapper;

import com.fjnldx.ks.pojo.mesDoMain.*;
import com.fjnldx.ks.pojo.request.ScjhReq2;
import com.fjnldx.ks.pojo.request.ScjhReq3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
@Mapper
public interface MesScjhMapper {
    /**
     * 查询各工序理论总产量
     */
    @Select("SELECT b.gongxu, count(b.devSn) AS devNum, ROUND(sum(a.tc), 3) AS zc FROM mes_dinger a,mes_newscjh b WHERE a.pz=b.pz AND a.gongxu=b.gongxu AND DATEDIFF(b.time,date(now()))=0 GROUP BY b.gongxu")
    List<ScjhZc> getMesScjhByTime();
    /**
     * 查询各工序各设备的理论台产
     */
    @Select("SELECT b.devSn,b.pz,b.scjhbh, a.tc,b.time FROM mes_dinger a,mes_newscjh b WHERE a.pz=b.pz AND DATEDIFF(b.time,date(now()))=0 AND a.gongxu=#{gongxu} AND devSn=#{devSn}")
    List<ScjhTc> getMesScjhTcByDevSn(ScjhReq2 scjhReq2);
    /**
     * 查询细纱实际总产
     */
    @Select("SELECT a.gongxu,count(a.devSn) AS devNum,sum(a.tc) AS sjzc FROM\n" +
            "(SELECT a.devSn,b.gongxu,ROUND((a.zcd*b.dl*b.ds)/100000, 3) AS tc FROM\n" +
            "(SELECT a.devSn,(b.zcd-a.zcd)%999999 AS zcd FROM\n" +
            "(SELECT devSn,zcd,time FROM (SELECT devSn,zcd,time,ROW_NUMBER() OVER(PARTITION BY devSn ORDER BY time ASC) AS Row_Sort FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0) a WHERE a.Row_Sort=1) a,\n" +
            "(SELECT devSn,zcd,time FROM (SELECT devSn,zcd,time,ROW_NUMBER() OVER(PARTITION BY devSn ORDER BY time DESC) AS Row_Sort FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0) a WHERE a.Row_Sort=1) b\n" +
            "WHERE a.devSn=b.devSn) a,\n" +
            "(SELECT a.devSn,a.pz,a.gongxu,a.time,b.dl,b.ds FROM mes_newscjh a,mes_dinger b WHERE a.pz=b.pz AND a.gongxu=b.gongxu AND DATEDIFF(a.time,date(now()))=0) b \n" +
            "WHERE a.devSn = b.devSn ) a\n" +
            "GROUP BY a.gongxu")
    List<ScjhSjZc> getMesScjhSjZcXiSha();
    /**
     * 根据设备号查询细纱实际台产
     */
    @Select("SELECT a.devSn,b.pz,b.gongxu,b.time,b.scjhbh ,b.dcg,b.gh,ROUND((a.zcd*b.dl*b.ds)/10000, 3) AS tc FROM\n" +
            "(SELECT a.devSn,(b.zcd-a.zcd)%999999 AS zcd FROM\n" +
            "(SELECT devSn,zcd,time FROM (SELECT devSn,zcd,time,ROW_NUMBER() OVER(PARTITION BY devSn ORDER BY time ASC) AS Row_Sort FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0) a WHERE a.Row_Sort=1) a,\n" +
            "(SELECT devSn,zcd,time FROM (SELECT devSn,zcd,time,ROW_NUMBER() OVER(PARTITION BY devSn ORDER BY time DESC) AS Row_Sort FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0) a WHERE a.Row_Sort=1) b\n" +
            "WHERE a.devSn=b.devSn) a,\n" +
            "(SELECT a.devSn,a.pz,a.gongxu,a.time,a.scjhbh,b.dl,b.ds,a.dcg,a.gh FROM mes_newscjh a,mes_dinger b WHERE a.pz=b.pz AND a.gongxu=b.gongxu AND a.gongxu=c.gongxu AND a.devSn=c.devSn AND DATEDIFF(a.time,date(now()))=0 ) b \n" +
            "WHERE a.devSn = b.devSn AND b.gongxu=\"细纱\" AND a.devSn=#{devSn}")
    List<ScjhTc> getMesScjhSjTcXiSha(ScjhReq3 scjhReq3);

    /**
     * 查询各工序品种理论总产
     */
    @Select("SELECT a.pz,a.gongxu,count(a.tc) AS zc FROM \n" +
            "(SELECT b.devSn,b.pz,b.scjhbh, a.tc,b.time,b.gongxu FROM mes_dinger a,mes_newscjh b WHERE a.pz=b.pz AND a.gongxu=b.gongxu AND DATEDIFF(b.time,date(now()))=0 ) a\n" +
            "GROUP BY a.pz,a.gongxu HAVING a.gongxu=#{gongxu}")
    List<PzZc> getMesScjhLlPzZc(String gongxu);
    /**
     * 查询各工序品种实际总产
     */
    @Select("SELECT a.gongxu,a.pz,count(a.devSn) AS devNum,sum(a.tc) AS sjzc FROM\n" +
            "\t(SELECT a.devSn,b.gongxu,b.pz,ROUND((a.zcd*b.dl*b.ds)/100000, 3) AS tc FROM\n" +
            "\t(SELECT a.devSn,(b.zcd-a.zcd)%999999 AS zcd FROM\n" +
            "\t(SELECT devSn,zcd,time FROM (SELECT devSn,zcd,time,ROW_NUMBER() OVER(PARTITION BY devSn ORDER BY time ASC) AS Row_Sort FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0) a WHERE a.Row_Sort=1) a,\n" +
            "\t(SELECT devSn,zcd,time FROM (SELECT devSn,zcd,time,ROW_NUMBER() OVER(PARTITION BY devSn ORDER BY time DESC) AS Row_Sort FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0) a WHERE a.Row_Sort=1) b\n" +
            "\tWHERE a.devSn=b.devSn) a,\n" +
            "\t(SELECT a.devSn,a.pz,a.gongxu,a.time,b.dl,b.ds FROM mes_newscjh a,mes_dinger b WHERE a.pz=b.pz AND a.gongxu=b.gongxu AND DATEDIFF(a.time,date(now()))=0) b \n" +
            "\tWHERE a.devSn = b.devSn ) a\n" +
            "\tGROUP BY a.gongxu,a.pz")
    List<PzSjZc> getMesScjhSjPzZcXisha();

}

